var mysql = require('mysql');

var db =mysql.createConnection({
    host:'127.0.0.1',
    user:'examsys',
    password:'examsys',
    database:'sys_packagingfunction'
});

db.connect();
var dbconfig = {};


/**
 * page是可带模糊查询的分页查询方法
 * @param {*} sql 查询使用的sql语句
 * @param {*} sqlpara 前台传递的查询条件
 * @param {*} pageIndex 当前页码
 * @param {*} callback 回调
 */
dbconfig.page = function(sql,sqlpara,callback){
    if(sqlpara.selectCondition){
        sql += " WHERE 1=1 "
        for( var i in sqlpara.selectCondition){
            sql += " and "+sqlpara.selectCondition[i].conditionName+" "+" "+sqlpara.selectCondition[i].operator+" "+" "+sqlpara.selectCondition[i].value+" ";
        }
    }else{
        sql += " WHERE 1=1 ";
    }
    var where = "";
    var num =0;
    for(var para in sqlpara.searcher){
        if(sqlpara.searcher[para]!=''){
            num++;
        }
    }
    if(num>0){
        for(var para in sqlpara.searcher){
            if(sqlpara.searcher[para]!=''){
                where += " and "+para+" = '"+sqlpara.searcher[para]+"' ";
            }
        }
    }
    var result = {};
    console.log(sql+where)
    console.log(sqlpara.searcher)
    db.query(sql+where,sqlpara.searcher,function (err,data){
        if(err) throw err;
        else{
            result.pageIndex = sqlpara.pageIndex;
            var row = [];
            result.total = data.length;
            result.countPage = Math.ceil(result.total / 5);
            x=(sqlpara.pageIndex-1)*5;
            y = (sqlpara.pageIndex*5)>data.length?data.length:(sqlpara.pageIndex*5);
            for(var i=x;i<y;i++){
                row[row.length] = data[i];
            }
            result.rows = row;
            callback(result);
        }
    })
}


dbconfig.insert = function(sql,sqlpara,callback){
    var value = "";
    var num =0;
    for(var para in sqlpara ){
        if(num==0 && sqlpara[para]!='' && para != 'primaryKey' && para != 'returnUrl'){
            value += " ( "+para+" ";
            num++;
        }else if(num!=0 && sqlpara[para]!='' && para != 'primaryKey' && para != 'returnUrl'){
            value += ","+para+" ";
            num++;
        }
    }
    value +=" )";
    num =0;
    value += " VALUES ";
    for(var para in sqlpara){
        if(num==0 && sqlpara[para]!='' && para != 'primaryKey' && para != 'returnUrl'){
            value += " ( '"+sqlpara[para]+"'";
            num++;
        }else if(num!=0 && sqlpara[para]!='' && para != 'primaryKey' && para != 'returnUrl'){
            value += ",'"+sqlpara[para]+"'";
            num++;
        }
    }
    value +=")";
    db.query(sql+value,function(err,data){
        if(err) throw err;
        else{
            if(data.affectedRows == 1){
                callback({result:'succeed'});
            }
        }
    });
}


dbconfig.update = function(sql,sqlpara,callback){
    var value = "";
    var num =0;
    console.log(sqlpara);
    for(var para in sqlpara){
        if(para!='primaryKey' && para!='primaryKeyName' && para != sqlpara['primaryKey']){
            if(num==0 && sqlpara[para]!='' && para != 'primaryKey' && para != 'returnUrl'){
                value += " "+para+" = '"+sqlpara[para]+"'";
                num++;
            }else if(num!=0 && sqlpara[para]!='' && para != 'primaryKey' && para != 'returnUrl'){
                value += ",  "+para+" = '"+sqlpara[para]+"'";
                num++;
            }
        }
    }
    for(var para in sqlpara){
        value += " WHERE "+sqlpara['primaryKey']+" = "+sqlpara[sqlpara['primaryKey']];
        break;
    }
    console.log(sql+value)
    db.query(sql+value,function(err,data){
        if(err) throw err;
        else{
            if(data.affectedRows == 1){
                callback({result:'succeed'});
            }
        }
    });
}





dbconfig.delete = function(sql,para,callback){
    
    db.query(sql,[],function(err,data){
        if(err) throw err;
        else{
            // console.log(data.affectedRows);
            if(data.affectedRows == 1){
                callback({result:'succeed'});
            }
        }
    })
}
module.exports = dbconfig;









